﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using TecBaseObjects.Models;
using TecBaseObjects.Objects;

namespace TecData.Layer {

    internal class CommonDataLayer: BaseSqlDataLayer {

        #region Members

        private string loadOrganisationsShortListStatement = "dbo.GetOrganisationShortList";
        private string loadJobsStatement = "Job.GetJobs";
        private string saveJobStatement = "Job.SaveJob";
        private string loadPlzOrtListStatement = "dbo.GetPostleitzahlOrtList";
        private string loadPlzOrtStatement = "dbo.GetPostleitzahlOrt";

        #endregion

        #region Load

        public virtual List<JobObj> Jobs(int ProjectID) {
            List<JobObj> retList = new List<JobObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadJobsStatement, new SqlParameter("@ProjectID", ProjectID))) {
                foreach (DataRow dr in dt.Rows) {
                    JobObj myJob = GetJobFromDataRow(dr);
                    retList.Add(myJob);
                }
            }
            return retList;
        }

        public virtual List<JobObj> Jobs(int CashSaleID, bool all) {
            List<JobObj> retList = new List<JobObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadJobsStatement, new SqlParameter("@CashSaleID", CashSaleID))) {
                foreach (DataRow dr in dt.Rows) {
                    JobObj myJob = GetJobFromDataRow(dr);
                    retList.Add(myJob);
                }
            }
            return retList;
        }

        public virtual List<JobObj> Jobs(int UserID, byte all) {
            List<JobObj> retList = new List<JobObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadJobsStatement, new SqlParameter("@UserID", UserID))) {
                foreach (DataRow dr in dt.Rows) {
                    JobObj myJob = GetJobFromDataRow(dr);
                    retList.Add(myJob);
                }
            }
            return retList;
        }

        public virtual List<JobObj> Jobs(Guid JobID) {
            List<JobObj> retList = new List<JobObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadJobsStatement, new SqlParameter("@JobID", JobID))) {
                foreach (DataRow dr in dt.Rows) {
                    JobObj myJob = GetJobFromDataRow(dr);
                    retList.Add(myJob);
                }
            }
            return retList;
        }

        public virtual List<JobObj> Jobs(short Status) {
            List<JobObj> retList = new List<JobObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadJobsStatement, new SqlParameter("@Status", Status))) {
                foreach (DataRow dr in dt.Rows) {
                    JobObj myJob = GetJobFromDataRow(dr);
                    retList.Add(myJob);
                }
            }
            return retList;
        }

        public virtual List<string> LoadMitarbeiter() {
            List<string> ma = new List<string>();
            //toDo: Anbindung Data

            return ma;
        }

        public virtual List<OrganisationsShortListObj> LoadOrganisationsShortList() {
            List<OrganisationsShortListObj> retList = new List<OrganisationsShortListObj>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadOrganisationsShortListStatement)) {
                foreach (DataRow dr in dt.Rows) {
                    OrganisationsShortListObj ret = new OrganisationsShortListObj();
                    ret.Abteilung = Convert.ToString(dr["Abteilung"]);
                    ret.Adresse = Convert.ToString(dr["Adresse"]);
                    //ret.OrgData = Convert.ToString(dr["OrgData"]);
                    if (dr["OrgKey"].ToString() != "") {
                        ret.OrgKey = Convert.ToInt32(dr["OrgKey"]);
                    }
                    ret.OrgName = Convert.ToString(dr["OrgName"]);
                    ret.Prefix = Convert.ToString(dr["Prefix"]);
                    ret.RechtsForm = Convert.ToString(dr["RechtsForm"]);
                    ret.Strasse = Convert.ToString(dr["Strasse"]);
                    ret.Suchbegriff = Convert.ToString(dr["Suchbegriff"]);
                    ret.ZusätzeFW = Convert.ToString(dr["ZusätzeFW"]);
                    retList.Add(ret);
                }
            }
            return retList;
        }

        public virtual List<PlzOrt> LoadPlzOrtList(bool withNull) {
            List<PlzOrt> ret = new List<PlzOrt>();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadPlzOrtListStatement, new SqlParameter("@WithNull", withNull))) {
                foreach (DataRow dr in dt.Rows) {
                    PlzOrt pl = new PlzOrt();
                    pl.EPLZ = (int)dr["EPLZ"];
                    if (dr["Bundesland"].ToString() != "") {
                        pl.Bundesland = Convert.ToUInt16(dr["Bundesland"]);
                    }
                    if (dr["Lkz"].ToString() != "") {
                        pl.Lkz = (string)dr["Lkz"];
                    }
                    if (Convert.ToString(dr["Ort"]) != "") {
                        pl.Ort = (string)dr["Ort"];
                    }
                    if (Convert.ToString(dr["Plz"]) != "") {
                        pl.Plz = (string)dr["Plz"];
                    }
                    if (dr["Zone"].ToString() != "") {
                        pl.Zone = Convert.ToUInt16(dr["Zone"]);
                    }
                    ret.Add(pl);
                }
            }
            return ret;
        }

        public virtual PlzOrt LoadPlzOrt(int EPLZ) {
            PlzOrt ret = new PlzOrt();
            using (DataTable dt = this.GetDataTableFromStoredProcedure(this.loadPlzOrtStatement, new SqlParameter("@EPLZ", EPLZ))) {
                foreach (DataRow dr in dt.Rows) {
                    ret.EPLZ = (int)dr["EPLZ"];
                    if (dr["Bundesland"].ToString() != "") {
                        ret.Bundesland = Convert.ToUInt16(dr["Bundesland"]);
                    }
                    if (dr["Lkz"].ToString() != "") {
                        ret.Lkz = (string)dr["Lkz"];
                    }
                    if (Convert.ToString(dr["Ort"]) != "") {
                        ret.Ort = (string)dr["Ort"];
                    }
                    if (Convert.ToString(dr["Plz"]) != "") {
                        ret.Plz = (string)dr["Plz"];
                    }
                    if (dr["Zone"].ToString() != "") {
                        ret.Zone = Convert.ToUInt16(dr["Zone"]);
                    }
                }
            }
            return ret;
        }


        #endregion

        #region Save

        public virtual void SaveJob(JobObj job) {
            int ret = 0;
            SqlParameter[] parameters = GetParameters(job);
            ret = this.ExecuteStoredProcedure(saveJobStatement, parameters);
            if (ret != 0) {
                Debug.Print("!=0");
            }
            
        }

        #endregion

        #region Functions

        private JobObj GetJobFromDataRow(DataRow dr) {
            TimeSpan endTime;
            TimeSpan startTime;
            short myStatus;
            Guid jobID;
            JobObj job = new JobObj();
            if (Convert.ToString(dr["CashSaleID"]) != "") {
                job.CashSaleID = Convert.ToInt32(dr["CashSaleID"]);
            }
            job.Datum = Convert.ToDateTime(dr["Datum"]);
            job.Einheit = Convert.ToString(dr["Einheit"]);
            if (TimeSpan.TryParse(Convert.ToString(dr["EndTime"]), out endTime)) {
                job.EndTime = endTime;
            }
            if (TimeSpan.TryParse(Convert.ToString(dr["StartTime"]), out startTime)) {
                job.StartTime = startTime;
            }
            job.Fahrzeug = Convert.ToString(dr["Fahrzeug"]);
            if (Guid.TryParse(Convert.ToString(dr["JobID"]), out jobID)) {
                job.JobID = jobID;
            }
            job.JobType = Convert.ToInt32(dr["JobType"]);
            job.Leistung = Convert.ToString(dr["Leistung"]);
            job.LeistungProzent = Convert.ToDouble(dr["LeistungProzent"]);
            job.LeistungZeit = Convert.ToDouble(dr["LeistungZeit"]);
            if (Convert.ToString(dr["ProjectID"]) != "") {
                job.ProjectID = Convert.ToInt32(dr["ProjectID"]);
            }
            job.ProjectName = Convert.ToString(dr["ProjektName"]);
            if (short.TryParse(Convert.ToString(dr["Status"]), out myStatus)) {
                job.Status = myStatus;
            }
            job.Title = Convert.ToString(dr["Title"]);
            job.UserID = Convert.ToInt32(dr["UserID"]);
            job.Area = Convert.ToString(dr["Area"]);
            job.SubArea = Convert.ToString(dr["SubArea"]);
            job.Sb = Convert.ToString(dr["Sb"]);
            job.Ss = Convert.ToString(dr["Ss"]);
            job.File = Convert.ToString(dr["Datei"]);
            job.Fa = Convert.ToString(dr["Fa"]);
            job.Vr = Convert.ToString(dr["Vr"]);

            return job;
        }

        protected virtual SqlParameter[] GetParameters(JobObj job) {
            SqlParameter[] ps = new SqlParameter[24];

            ps[0] = new SqlParameter("@JobID", job.JobID);
            ps[1] = new SqlParameter("@UserID", job.UserID);
            ps[2] = new SqlParameter("@JobType", job.JobType);
            ps[3] = new SqlParameter("@Title", job.Title);
            ps[4] = new SqlParameter("@Einheit", job.Einheit);
            ps[5] = new SqlParameter("@Fahrzeug", job.Fahrzeug);
            ps[6] = new SqlParameter("@Datum", job.Datum);
            ps[7] = new SqlParameter("@StartTime", job.StartTime);
            ps[8] = new SqlParameter("@EndTime", job.EndTime);
            ps[9] = new SqlParameter("@Leistung", job.Leistung);
            ps[10] = new SqlParameter("@LeistungProzent", job.LeistungProzent);
            ps[11] = new SqlParameter("@LeistungZeit", job.LeistungZeit);
            ps[12] = new SqlParameter("@ProjektName", job.ProjectName);
            ps[13] = new SqlParameter("@Status", job.Status);
            ps[14] = new SqlParameter("@CashSaleID", job.CashSaleID);
            ps[15] = new SqlParameter("@ProjectID", job.ProjectID);
            ps[16] = new SqlParameter("@ErrorMessage", "");
            ps[17] = new SqlParameter("@Area", job.Area);
            ps[18] = new SqlParameter("@SubArea", job.SubArea);
            ps[19] = new SqlParameter("@Sb", job.Sb);
            ps[20] = new SqlParameter("@Ss", job.Ss);
            ps[21] = new SqlParameter("@Datei", job.File);
            ps[22] = new SqlParameter("@Fa", job.Fa);
            ps[23] = new SqlParameter("@Vr", job.Vr);

            return ps;
        }

        #endregion

    }
}
